rel-vars? - Mailing list pgsql-general

I'm working on this thing where touring musicians can find venues based on
criteria such as distance from a given zip code and pay scale and capacity
and openness to new, unknown bands for my starving musician clients.

There's a venues table with your basic info, including zip as a text field,
and the Tiger (US Census Bureau) data to relate zip codes to
longitude/latitude and a  distance formula to be able to find zip codes
within a given distance of each other.

The substr and int4 casting business in there is to get rid of any +4
parts, and I made the Tiger data be integer as much as possible for
indexing speed reasons.  [Possibly through a misunderstanding of indexing
int4 versus text fields, but it shouldn't affect this.]

Whew.

Some queries work great.  Some don't.

select venues.name from venues, zips as venuezip, zips as wherezip where
1=1 and wherezip.zip = 10025 and substr(venues.zip, 1, 5)::int4 =
venuezip.zip and distance(venuezip.latitude, venuezip.longitude,
wherezip.latitude, wherezip.longitude) < 10;
name
--------------------
Arlene Grocery
ACME Underground
CBGB Gallery
Brownies
Hotel Galvez
Baggot Inn
Neo Lounge
Fast Folk Cafe
Knitting Factory
Cornelia Street Cafe
S.O.B.s
Gaslight
(12 rows)

The above is good.  It finds a bunch of venues in Manhattan, and is
actually more-or-less correct for the data given.  [My distance formula
sucks, but so what?]

But the below, trying to add in venues with a capacity of 100 or greater or
with unknown capacity is bad.  I can specify the 100 part only, or the null
part only, but together they throw it for a loop.  Why?

select venues.name from venues, zips as venuezip, zips as wherezip where
1=1 and wherezip.zip = 10025 and substr(venues.zip, 1, 5)::int4 =
venuezip.zip and distance(venuezip.latitude, venuezip.longitude,
wherezip.latitude, wherezip.longitude) < 10 and (venues.capacity > 100 or
venues.capacity is null);

ERROR:  ExecInitIndexScan: both left and right op's are rel-vars


The first time I got this, I had screwed up my self-join tables, so I
thought it meant something kinda like trying to solve formulae in N
variable with less than N - 1 equations:  It was sort of an
open-ended/infinite set that would have resulted had the query been able to
function.  You (generally) just can't do that.  But now, I just don't see
how adding in the capacity of the venue is messing that up, so I'm not sure
what this message means...

I did an explain on the bad query, and don't understand what it's saying,
but it doesn't look bad to me...

Result  (cost=66485.78 size=0 width=0)
  ->  Nested Loop  (cost=66485.78 size=21613 width=44)
        ->  Nested Loop  (cost=28.88 size=32418 width=36)
              ->  Seq Scan on venues  (cost=6.33 size=11 width=24)
              ->  Index Scan using zipsindex on venuezip  (cost=2.05
size=29470 width=12)
        ->  Index Scan using zipstateindex on wherezip  (cost=2.05 size=2
width=8)


PostgreSQL 6.3.2, if that matters.  I've asked the ISP to upgrade, but...

Oh, you can see it (not) work at:

www.chatmusic.com/venues.htm

You can leave out the zip code info, and it works fine.  Most of the venues
don't have capacity or pay or openness or genre info yet, so the results
you get without zip codes might seem awfully sparse.  In fact, for testing
purposes, only Uncommon Ground has that kind of info so far.

-- "TANSTAAFL" Rich lynch@cognitivearts.com   webmaster@  and www. all of:
R&B/jazz/blues/rock - jademaze.com      music industry org - chatmusic.com
acoustic/funk/world-beat - astrakelly.com   sculptures - olivierledoux.com
my own nascent company - l-i-e.com   cool coffeehouse - uncommonground.com